/*
 EXEC sp_search_receipts '%', '%', -1, -1, '-1'

 select * FROm PurchaseOrder_Table
*/

ALTER  PROC sp_search_receipts @receiptNumber VARCHAR(50), @poNumber VARCHAR(50), @vendorId INT, @processStepId INT, @year VARCHAR(10)
AS
BEGIN
	DECLARE @SQLString NVARCHAR(2000)
	DECLARE @ParamDefinition NVARCHAR(500)
	DECLARE @Receipt_ID INT
	DECLARE @Receipt_Number VARCHAR(50)
	DECLARE @PurchaseOrder_ID INT
	DECLARE @PurchaseOrder_Number VARCHAR(50)
	DECLARE @Receipt_Date VARCHAR(50)
	DECLARE @Vendor_ID INT
	DECLARE @Vendor_Name VARCHAR(50)
	DECLARE @count INT
	DECLARE @PURCHASEORDER_TYPE INT
	DECLARE @PR_STATUS VARCHAR(50)

	DECLARE @results TABLE ( Receipt_Id INT, ReceiptNumber VARCHAR(50), PurchaseOrder_Id INT, PONumber VARCHAR(50), Vendor_Name VARCHAR(50), ReceiptDate VARCHAR(50), PURCHASEORDER_TYPE INT, PR_STATUS VARCHAR(50) )
	SET @Count = 0	

	SET @SQLString = N'DECLARE ReceiptCursor CURSOR FOR SELECT PR.Receipt_Id, PR.Receipt_Number, PR.PurchaseOrder_ID, PO.PurchaseOrder_No, V.V_Name, CONVERT( VARCHAR, PR.Receipt_Date, 101 ) AS Receipt_Date, PURCHASEORDER_TYPE, PR.PR_STATUS FROM PurchaseOrder_Receipt_Table PR, PurchaseOrder_Table PO, Vendor V WHERE PR.PurchaseOrder_ID = PO.PurchaseOrder_ID AND PR.Vendor_Id = V.Vendor_ID '
	SET @ParamDefinition = N'@Prm_ReceiptNumber VARCHAR(50), @Prm_PONumber VARCHAR(50), @Prm_Vendor_ID INT, @Prm_ProcessStep_ID INT, @Prm_Year VARCHAR(10)'

	IF ( @receiptNumber IS NOT NULL AND LTRIM(RTRIM( @receiptNumber ) ) <> '' )
	BEGIN
		SET @SQLString = @SQLString + ' AND PR.Receipt_Number like @Prm_ReceiptNumber '
	END

	IF ( @poNumber IS NOT NULL AND LTRIM(RTRIM( @poNumber ) ) <> '' )
	BEGIN
		SET @SQLString = @SQLString + ' AND PO.PurchaseOrder_No like @Prm_PONumber '
	END

	IF ( @vendorId IS NOT NULL AND @vendorId <> -1 )
	BEGIN
		SET @SQLString = @SQLString + ' AND V.Vendor_Id = @Prm_Vendor_ID'
	END

	IF ( @year IS NOT NULL AND LTRIM(RTRIM( @year ) ) <> '-1' )
	BEGIN
		SET @SQLString = @SQLString + ' AND YEAR( PR.Receipt_Date ) = @Prm_Year'
	END

	SET @SQLString = @SQLString + ' ORDER BY PR.Receipt_Date'

	PRINT @SQLString

	EXECUTE sp_executesql @SQLString, @ParamDefinition, @Prm_ReceiptNumber = @receiptNumber, @Prm_PONumber = @poNumber, @Prm_Vendor_ID = @vendorId, @Prm_ProcessStep_Id = @processStepId, @Prm_Year = @year
	
	OPEN ReceiptCursor
	FETCH NEXT FROM ReceiptCursor INTO @Receipt_ID, @Receipt_Number, @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @Receipt_Date, @PURCHASEORDER_TYPE, @PR_STATUS
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ( @processStepId IS NOT NULL AND @processStepId <> -1 )
		BEGIN
			SET @Count = 0
			SELECT @Count = COUNT(*) FROM PurchaseOrder_Receipt_LineItem_Table WHERE Receipt_id = @Receipt_ID AND ProcessStep_Id = @processStepId
			IF @Count <> 0
			BEGIN
				INSERT INTO @results( Receipt_Id, ReceiptNumber, PurchaseOrder_Id, PONumber, Vendor_Name, ReceiptDate, PURCHASEORDER_TYPE, PR_STATUS )
				VALUES ( @Receipt_ID, @Receipt_Number, @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @Receipt_Date, @PURCHASEORDER_TYPE, @PR_STATUS )
			END
		END
 		ELSE
		BEGIN
			INSERT INTO @results( Receipt_Id, ReceiptNumber, PurchaseOrder_Id, PONumber, Vendor_Name, ReceiptDate, PURCHASEORDER_TYPE, PR_STATUS )
			VALUES ( @Receipt_ID, @Receipt_Number, @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @Receipt_Date, @PURCHASEORDER_TYPE, @PR_STATUS )
		END

		FETCH NEXT FROM ReceiptCursor INTO @Receipt_ID, @Receipt_Number, @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @Receipt_Date, @PURCHASEORDER_TYPE, @PR_STATUS
	END
	CLOSE ReceiptCursor
	DEALLOCATE ReceiptCursor

	SELECT * FROM @results
END